﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Security.Policy;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using System.Web.Http;
using System.Web.Http.Results;
using System.Windows.Forms;
using System.Xml.Linq;
using Google.Protobuf.WellKnownTypes;
using log4net;
using MySql.Data.MySqlClient;
using MySqlX.XDevAPI.Relational;
using Newtonsoft.Json;
using static CloudApiServer.Controllers.DemoController;

namespace CloudApiServer.Controllers
{
    /// <summary>
    /// 东软三方对接接口控制器
    /// </summary>
    [RoutePrefix("public")]
    public class FsiApiNeusJiangsuController : ApiController
    {
        [HttpGet]
        [HttpPost]
        public IHttpActionResult NeusJiangsuNotFound(HttpRequestMessage request)
        {
            string url = request.RequestUri.ToString();
            string Method = request.Method.ToString();
            return Json(new { code = -1, message = $"请求的路径【{url}】方法【{Method}】不存在！请检查传入的地址是否有误或联系管理员！", data = "" });
        
        }
        /// <summary>
        /// base64转string
        /// </summary>
        /// <param name="base64">base64编码</param>
        /// <returns>string值</returns>
        /// <exception cref="Exception"></exception>
        public string Base64toString(string base64)
        {
            try
            {
                // 解码Base64字符串为字节数组  
                byte[] decodedBytes = Convert.FromBase64String(base64);
                // 将字节数组转换为字符串（假设原始数据是UTF-8编码的）  
                string decodedString = Encoding.UTF8.GetString(decodedBytes);
                // 输出解码后的字符串  
                return decodedString;
            }
            catch (FormatException)
            {
                throw new Exception("Base64转换失败！请检查方法入参！当前入参的base64编码：" + base64);
            }
            catch (Exception ex)
            {
                throw new Exception($"Base64转换失败！错误内容：{ex.Message} 异常来源：{ex.StackTrace}");
            }
        }

        /// <summary>
        /// 东软商品信息同步 吉林临时表上传
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [Route("syncproduct")]
        [Route("syncproductTemp")]
        [HttpPost]
        public async Task<IHttpActionResult> syncproduct(HttpRequestMessage request)
        {
            int codestate = -1;
            int row = 0;
            string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                try
                {
                    if (FsiDatainfo.stateNeusJiangsuApi==false)
                    {
                        throw new Exception("该接口已被控制台停用！请联系管理员！");
                    }
                    var NeusJiangsuHeader = JsonConvert.DeserializeObject<NeusJiangsuHeader>(request.Content.ReadAsStringAsync().Result);
                    string appKey = "";
                    string data = Base64toString(NeusJiangsuHeader.data);
                    if (Request.Headers.TryGetValues("AppKey", out var appKeyValues))
                    {
                        appKey = appKeyValues.FirstOrDefault();
                    }
                    if (string.IsNullOrEmpty(appKey) || string.IsNullOrEmpty(data))
                    {
                        codestate = 2008;
                        throw new Exception("参数校验失败：请求的appkey或data节点为空！");
                    }
                    DataTable dt = new DataTable();
                    dt = JsonConvert.DeserializeObject<DataTable>(data);
                    DateTime dateTime = DateTime.Now;
                    await conn.OpenAsync();
                    using (MySqlTransaction transaction = conn.BeginTransaction())
                    {
                        using (MySqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.Transaction = transaction;
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                if (dt.Rows[i]["synctype"].ToString()=="1")
                                {
                                    try
                                    {
                                        cmd.CommandText = "INSERT INTO neus_jiangsu_syncproduct  VALUES ( @orgcode, @oldproductno,@productname, @barcode, " +
                                            "@factoryname, @producttype, @unit, @approvedocno, @otcflag, @zyflag, @saleprice, @memo, @securityno," +
                                            " @centCodg, @createdate,@itemclassification,@create_time,@update_time)";
                                        cmd.Parameters.AddWithValue("@orgcode", dt.Rows[i]["orgcode"].ToString());
                                        cmd.Parameters.AddWithValue("@oldproductno", dt.Rows[i]["oldproductno"].ToString());
                                        cmd.Parameters.AddWithValue("@productname", dt.Rows[i]["productname"].ToString());
                                        if (dt.Columns.Contains("barcode") == true)
                                        {
                                            cmd.Parameters.AddWithValue("@barcode", dt.Rows[i]["barcode"].ToString());
                                        }
                                        cmd.Parameters.AddWithValue("@factoryname", dt.Rows[i]["factoryname"].ToString());
                                        cmd.Parameters.AddWithValue("@producttype", dt.Rows[i]["producttype"].ToString());
                                        cmd.Parameters.AddWithValue("@unit", dt.Rows[i]["unit"].ToString());
                                        if (dt.Columns.Contains("approvedocno") == true)
                                        {
                                            cmd.Parameters.AddWithValue("@approvedocno", dt.Rows[i]["approvedocno"].ToString());
                                        }
                                        cmd.Parameters.AddWithValue("@otcflag", dt.Rows[i]["otcflag"].ToString());
                                        cmd.Parameters.AddWithValue("@zyflag", dt.Rows[i]["zyflag"].ToString());
                                        cmd.Parameters.AddWithValue("@saleprice", dt.Rows[i]["saleprice"].ToString());
                                        if (dt.Columns.Contains("memo") == true)
                                        {
                                            cmd.Parameters.AddWithValue("@memo", dt.Rows[i]["memo"].ToString());
                                        }
                                        cmd.Parameters.AddWithValue("@securityno", dt.Rows[i]["securityno"].ToString());
                                        if (dt.Columns.Contains("centCodg") == true)
                                        {
                                            cmd.Parameters.AddWithValue("@centCodg", dt.Rows[i]["centCodg"].ToString());
                                        }
                                        cmd.Parameters.AddWithValue("@createdate", dt.Rows[i]["createdate"].ToString());
                                        cmd.Parameters.AddWithValue("@itemclassification", dt.Rows[i]["itemclassification"].ToString());
                                        cmd.Parameters.AddWithValue("@create_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                        cmd.Parameters.AddWithValue("@update_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                        await cmd.ExecuteNonQueryAsync();
                                        cmd.Parameters.Clear();
                                    }
                                    catch (Exception ex)
                                    {
                                        transaction.Rollback();
                                        if (ex.Message.Contains("Duplicate entry"))
                                        {
                                            codestate = 2009;
                                            throw new Exception($"【{dt.Rows[i]["oldproductno"]}/{dt.Rows[i]["productname"]}】商品上传重复!");
                                        }
                                        else
                                        {
                                            codestate = -1;
                                            throw new Exception($"【{dt.Rows[i]["oldproductno"]}/{dt.Rows[i]["productname"]}】商品上传出错：{ex.Message}");
                                        }
                                    }
                                }
                                else if (dt.Rows[i]["synctype"].ToString() == "2")
                                {
                                    try
                                    {
                                        cmd.CommandText = "update neus_jiangsu_syncproduct  set  productname=@productname, barcode=@barcode, " +
                                            "factoryname=@factoryname, producttype=@producttype, unit=@unit, approvedocno=@approvedocno, otcflag=@otcflag, zyflag=@zyflag, saleprice=@saleprice, memo=@memo, securityno=@securityno," +
                                            " centCodg=@centCodg, createdate=@createdate,itemclassification=@itemclassification,update_time=@update_time where orgcode=@orgcode and oldproductno=@oldproductno ";
                                        cmd.Parameters.AddWithValue("@orgcode", dt.Rows[i]["orgcode"].ToString());
                                        cmd.Parameters.AddWithValue("@oldproductno", dt.Rows[i]["oldproductno"].ToString());
                                        cmd.Parameters.AddWithValue("@productname", dt.Rows[i]["productname"].ToString());
                                        cmd.Parameters.AddWithValue("@barcode", dt.Rows[i]["barcode"].ToString());
                                        cmd.Parameters.AddWithValue("@factoryname", dt.Rows[i]["factoryname"].ToString());
                                        cmd.Parameters.AddWithValue("@producttype", dt.Rows[i]["producttype"].ToString());
                                        cmd.Parameters.AddWithValue("@unit", dt.Rows[i]["unit"].ToString());
                                        if(dt.Columns.Contains("approvedocno") == true)
                                        {
                                            cmd.Parameters.AddWithValue("@approvedocno", dt.Rows[i]["approvedocno"].ToString());
                                        }
                                        cmd.Parameters.AddWithValue("@otcflag", dt.Rows[i]["otcflag"].ToString());
                                        cmd.Parameters.AddWithValue("@zyflag", dt.Rows[i]["zyflag"].ToString());
                                        cmd.Parameters.AddWithValue("@saleprice", dt.Rows[i]["saleprice"].ToString());
                                        if (dt.Columns.Contains("memo") == true)
                                        {
                                            cmd.Parameters.AddWithValue("@memo", dt.Rows[i]["memo"].ToString());
                                        }
                                        cmd.Parameters.AddWithValue("@securityno", dt.Rows[i]["securityno"].ToString());
                                        if (dt.Columns.Contains("centCodg") == true)
                                        {
                                            cmd.Parameters.AddWithValue("@centCodg", dt.Rows[i]["centCodg"].ToString());
                                        }
                                        cmd.Parameters.AddWithValue("@createdate", dt.Rows[i]["createdate"].ToString());
                                        cmd.Parameters.AddWithValue("@itemclassification", dt.Rows[i]["itemclassification"].ToString());
                                        cmd.Parameters.AddWithValue("@update_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                        row =  await cmd.ExecuteNonQueryAsync();
                                        cmd.Parameters.Clear();
                                    }
                                    catch (Exception ex)
                                    {
                                        codestate = -1;
                                        throw new Exception($"【{dt.Rows[i]["oldproductno"]}/{dt.Rows[i]["productname"]}】商品上传出错：{ex.Message}");
                                    }
                                    if (row < 1)
                                    {
                                        codestate = 2004;
                                        throw new Exception($"【{dt.Rows[i]["oldproductno"]}/{dt.Rows[i]["productname"]}】对应商品未找到!");
                                    }
                                }
                            }
                            cmd.Parameters.Clear();
                            transaction.Commit();
                            codestate = 200;
                            return Json(new { code = codestate, message = $"处理成功", data = "" });
                        }
                    }
                }
                catch (Exception ex)
                {
                    string Errnum = LogHelper.GetErrornumber();
                    return Json(new { code = codestate, message = $"[{Errnum}]调用东软三方自定义服务失败！请检查！错误内容：{ex.Message} " , data = "" });

                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        await conn.CloseAsync();
                    }
                }
            }
        }

        /// <summary>
        /// 东软商品库存同步
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [Route("syncstock")]
        [HttpPost]
        public async Task<IHttpActionResult> syncstock(HttpRequestMessage request)
        {
            int codestate = -1;
            int row = 0;
            string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                try
                {
                    if (FsiDatainfo.stateNeusJiangsuApi == false)
                    {
                        throw new Exception("该接口已被控制台停用！请联系管理员！");
                    }
                    var NeusJiangsuHeader = JsonConvert.DeserializeObject<NeusJiangsuHeader>(request.Content.ReadAsStringAsync().Result);
                    string appKey = "";
                    string data = Base64toString(NeusJiangsuHeader.data);
                    Console.WriteLine(data);
                    if (Request.Headers.TryGetValues("AppKey", out var appKeyValues))
                    {
                        appKey = appKeyValues.FirstOrDefault();
                    }
                    if (string.IsNullOrEmpty(appKey) || string.IsNullOrEmpty(data))
                    {
                        codestate = 2008;
                        throw new Exception("参数校验失败：请求的appkey或data节点为空！");
                    }
                    DataTable dt = new DataTable();
                    dt = JsonConvert.DeserializeObject<DataTable>(data);
                    DateTime dateTime = DateTime.Now;
                    await conn.OpenAsync();
                    using (MySqlTransaction transaction = conn.BeginTransaction())
                    {
                        using (MySqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.Transaction = transaction;
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                if (dt.Rows[i]["synctype"].ToString() == "1")
                                {
                                    try
                                    {
                                        cmd.CommandText = "INSERT INTO neus_jiangsu_stock  VALUES ( @orgcode, @oldproductno,@oldproductname,@lotno,@makedate, @invalidate,@num,@create_time,@update_time)";
                                        cmd.Parameters.AddWithValue("@orgcode", dt.Rows[i]["orgcode"].ToString());
                                        cmd.Parameters.AddWithValue("@oldproductno", dt.Rows[i]["oldproductno"].ToString());
                                        cmd.Parameters.AddWithValue("@oldproductname", dt.Rows[i]["oldproductname"].ToString());
                                        cmd.Parameters.AddWithValue("@num", dt.Rows[i]["productqty"].ToString());
                                        cmd.Parameters.AddWithValue("@lotno", dt.Rows[i]["lotno"].ToString());
                                        cmd.Parameters.AddWithValue("@makedate", dt.Rows[i]["makedate"].ToString());
                                        cmd.Parameters.AddWithValue("@invalidate", dt.Rows[i]["invalidate"].ToString());
                                        cmd.Parameters.AddWithValue("@create_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                        cmd.Parameters.AddWithValue("@update_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                        await cmd.ExecuteNonQueryAsync();
                                        cmd.Parameters.Clear();

                                        cmd.CommandText = "INSERT INTO neus_jiangsu_syncstock  VALUES ( @orgcode, @oldproductno,@oldproductname, @datatype, " +
                                            "@productqty, @lotno, @stockstatus, @memo, @makedate, @invalidate, @createdate, @purprice,@create_time,@update_time)";
                                        cmd.Parameters.AddWithValue("@orgcode", dt.Rows[i]["orgcode"].ToString());
                                        cmd.Parameters.AddWithValue("@oldproductno", dt.Rows[i]["oldproductno"].ToString());
                                        cmd.Parameters.AddWithValue("@oldproductname", dt.Rows[i]["oldproductname"].ToString());
                                        cmd.Parameters.AddWithValue("@datatype", dt.Rows[i]["datatype"].ToString());
                                        cmd.Parameters.AddWithValue("@productqty", dt.Rows[i]["productqty"].ToString());
                                        cmd.Parameters.AddWithValue("@lotno", dt.Rows[i]["lotno"].ToString());
                                        cmd.Parameters.AddWithValue("@stockstatus", dt.Rows[i]["stockstatus"].ToString());
                                        if (dt.Columns.Contains("memo") == true)
                                        {
                                            cmd.Parameters.AddWithValue("@memo", dt.Rows[i]["memo"].ToString());
                                        }
                                        cmd.Parameters.AddWithValue("@makedate", dt.Rows[i]["makedate"].ToString());
                                        cmd.Parameters.AddWithValue("@invalidate", dt.Rows[i]["invalidate"].ToString());
                                        cmd.Parameters.AddWithValue("@createdate", dt.Rows[i]["createdate"].ToString());
                                        cmd.Parameters.AddWithValue("@purprice", dt.Rows[i]["purprice"].ToString());
                                        cmd.Parameters.AddWithValue("@create_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                        cmd.Parameters.AddWithValue("@update_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                        await cmd.ExecuteNonQueryAsync();
                                        cmd.Parameters.Clear();
                                    }
                                    catch (Exception ex)
                                    {
                                        transaction.Rollback();
                                        if (ex.Message.Contains("Duplicate entry"))
                                        {
                                            codestate = 2009;
                                            throw new Exception($"【{dt.Rows[i]["oldproductno"]}/{dt.Rows[i]["oldproductname"]}】商品批次{dt.Rows[i]["lotno"]}上传重复!请传变更库存");
                                        }
                                        else
                                        {
                                            codestate = -1;
                                            throw new Exception($"【{dt.Rows[i]["oldproductno"]}/{dt.Rows[i]["oldproductname"]}】商品库存上传出错：{ex.Message}");
                                        }
                                    }


                                }
                                else if (dt.Rows[i]["synctype"].ToString() == "2")
                                {
                                    try
                                    {
                                        cmd.CommandText = "update neus_jiangsu_stock  set num=num+@num,update_time=@update_time " +
                                            "where orgcode=@orgcode and oldproductno=@oldproductno and lotno=@lotno ";
                                        cmd.Parameters.AddWithValue("@orgcode", dt.Rows[i]["orgcode"].ToString());
                                        cmd.Parameters.AddWithValue("@oldproductno", dt.Rows[i]["oldproductno"].ToString());
                                        cmd.Parameters.AddWithValue("@oldproductname", dt.Rows[i]["oldproductname"].ToString());
                                        cmd.Parameters.AddWithValue("@num", dt.Rows[i]["productqty"].ToString());
                                        cmd.Parameters.AddWithValue("@lotno", dt.Rows[i]["lotno"].ToString());
                                        cmd.Parameters.AddWithValue("@makedate", dt.Rows[i]["makedate"].ToString());
                                        cmd.Parameters.AddWithValue("@invalidate", dt.Rows[i]["invalidate"].ToString());
                                        cmd.Parameters.AddWithValue("@create_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                        cmd.Parameters.AddWithValue("@update_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                        row =await cmd.ExecuteNonQueryAsync();
                                        cmd.Parameters.Clear();

                                        cmd.CommandText = "INSERT INTO neus_jiangsu_syncstock  VALUES ( @orgcode, @oldproductno,@oldproductname, @datatype, " +
                                            "@productqty, @lotno, @stockstatus, @memo, @makedate, @invalidate, @createdate, @purprice,@create_time,@update_time)";
                                        cmd.Parameters.AddWithValue("@orgcode", dt.Rows[i]["orgcode"].ToString());
                                        cmd.Parameters.AddWithValue("@oldproductno", dt.Rows[i]["oldproductno"].ToString());
                                        cmd.Parameters.AddWithValue("@oldproductname", dt.Rows[i]["oldproductname"].ToString());
                                        cmd.Parameters.AddWithValue("@datatype", dt.Rows[i]["datatype"].ToString());
                                        cmd.Parameters.AddWithValue("@productqty", dt.Rows[i]["productqty"].ToString());
                                        cmd.Parameters.AddWithValue("@lotno", dt.Rows[i]["lotno"].ToString());
                                        cmd.Parameters.AddWithValue("@stockstatus", dt.Rows[i]["stockstatus"].ToString());
                                        if (dt.Columns.Contains("memo") == true)
                                        {
                                            cmd.Parameters.AddWithValue("@memo", dt.Rows[i]["memo"].ToString());
                                        }
                                        cmd.Parameters.AddWithValue("@makedate", dt.Rows[i]["makedate"].ToString());
                                        cmd.Parameters.AddWithValue("@invalidate", dt.Rows[i]["invalidate"].ToString());
                                        cmd.Parameters.AddWithValue("@createdate", dt.Rows[i]["createdate"].ToString());
                                        cmd.Parameters.AddWithValue("@purprice", dt.Rows[i]["purprice"].ToString());
                                        cmd.Parameters.AddWithValue("@create_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                        cmd.Parameters.AddWithValue("@update_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                        await cmd.ExecuteNonQueryAsync();
                                        cmd.Parameters.Clear();
                                    }
                                    catch (Exception ex)
                                    {
                                        codestate = -1;
                                        throw new Exception($"【{dt.Rows[i]["oldproductno"]}/{dt.Rows[i]["oldproductname"]}】商品库存上传出错：{ex.Message}");
                                    }
                                    if (row < 1)
                                    {
                                        codestate = 2004;
                                        throw new Exception($"该商品批号{dt.Rows[i]["lotno"]}尚未初始化库存，不允许变更传入，请先初始化该批次");
                                    }
                                }
                                else
                                {
                                    codestate = 2008;
                                    throw new Exception($"【{dt.Rows[i]["oldproductno"]}/{dt.Rows[i]["oldproductname"]}】上传出错：传入的synctype代码值有误，不为1或者2，当前传入：{dt.Rows[i]["synctype"]}");
                                }


                            }
                            cmd.Parameters.Clear();
                            transaction.Commit();
                            codestate = 200;
                            return Json(new { code = codestate, message = $"处理成功", data = "" });
                        }
                    }
                }
                catch (Exception ex)
                {
                    string Errnum = LogHelper.GetErrornumber();
                    return Json(new { code = codestate, message = $"[{Errnum}]调用东软三方自定义服务失败！请检查！错误内容：{ex.Message} " , data = "" });

                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        await conn.CloseAsync();
                    }
                }
            }
        }

        /// <summary>
        /// 东软商品订单交易同步
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [Route("syncorder")]
        [HttpPost]
        public async Task<IHttpActionResult> syncorder(HttpRequestMessage request)
        {
            int codestate = -1;
            string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                try
                {
                    if (FsiDatainfo.stateNeusJiangsuApi == false)
                    {
                        throw new Exception("该接口已被控制台停用！请联系管理员！");
                    }
                    var NeusJiangsuHeader = JsonConvert.DeserializeObject<NeusJiangsuHeader>(request.Content.ReadAsStringAsync().Result);
                    string appKey = "";
                    string data = Base64toString(NeusJiangsuHeader.data);
                    if (Request.Headers.TryGetValues("AppKey", out var appKeyValues))
                    {
                        appKey = appKeyValues.FirstOrDefault();
                    }
                    if (string.IsNullOrEmpty(appKey) || string.IsNullOrEmpty(data))
                    {
                        codestate = 2008;
                        throw new Exception("参数校验失败：请求的appkey或data节点为空！");
                    }
                    var order = JsonConvert.DeserializeObject<NeusJiangsuOrderdetail>(data);
                    Console.WriteLine(data);
                    int orderflag = order.saleFlag;
                    if (orderflag==1)
                    {
                        throw new Exception("参数校验失败：传入的订单类型标志为退款，请调用退费接口");
                    }
                    List <NeusJiangsuOrderdetailList> list = new List<NeusJiangsuOrderdetailList>();
                    list = order.orderdetailList;
                    if (list==null)
                    {
                        throw new Exception("参数校验失败：传入的订单商品列表为空");
                    }
                    DateTime dateTime = DateTime.Now;
                    await conn.OpenAsync();
                    string error = "";
                    using (MySqlTransaction transaction = conn.BeginTransaction())
                    {
                        using (MySqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.Transaction = transaction;
                            try
                            {
                                cmd.CommandText = "INSERT INTO neus_jiangsu_order  VALUES ( @orderno, @orgcode,@createdate,@realmoney,@memo,@dtllines, @saleFlag,@originalOrderno,@create_time,@update_time)";
                                cmd.Parameters.AddWithValue("@orderno", order.orderno);
                                cmd.Parameters.AddWithValue("@orgcode", order.orgcode);
                                cmd.Parameters.AddWithValue("@createdate", order.createdate);
                                cmd.Parameters.AddWithValue("@realmoney", order.realmoney);
                                cmd.Parameters.AddWithValue("@memo", order.memo);
                                cmd.Parameters.AddWithValue("@dtllines", order.dtllines);
                                cmd.Parameters.AddWithValue("@saleFlag", order.saleFlag);
                                cmd.Parameters.AddWithValue("@originalOrderno", order.originalOrderno);
                                cmd.Parameters.AddWithValue("@create_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                cmd.Parameters.AddWithValue("@update_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                await cmd.ExecuteNonQueryAsync();
                                cmd.Parameters.Clear();
                            }
                            catch (Exception ex)
                            {
                                transaction.Rollback();
                                codestate = -1;
                                throw new Exception($"订单汇总数据异常：{ex.Message}");
                            }

                            foreach (var item in list)
                            {
                                try
                                {
                                    cmd.CommandText = "select * from neus_jiangsu_syncproduct where oldproductno=@oldproductno and orgcode=@orgcode  ";
                                    cmd.Parameters.AddWithValue("@oldproductno", item.oldproductno);
                                    cmd.Parameters.AddWithValue("@orgcode", order.orgcode);
                                    using (MySqlDataReader reader = (MySqlDataReader)await cmd.ExecuteReaderAsync())
                                    {
                                        DataTable dt = new DataTable();
                                        dt.Load(reader);
                                        if (dt.Rows.Count < 1)
                                        {
                                            throw new Exception($"未查询到该商品的资料，请先推送商品。");
                                        }
                                    }
                                    cmd.Parameters.Clear();

                                    cmd.CommandText = "select * from neus_jiangsu_syncstock where oldproductno=@oldproductno and orgcode=@orgcode  ";
                                    cmd.Parameters.AddWithValue("@oldproductno", item.oldproductno);
                                    cmd.Parameters.AddWithValue("@orgcode", order.orgcode);
                                    using (MySqlDataReader reader = (MySqlDataReader)await cmd.ExecuteReaderAsync())
                                    {
                                        DataTable dt = new DataTable();
                                        dt.Load(reader);
                                        if (dt.Rows.Count < 1)
                                        {
                                            throw new Exception($"未查询到该商品的库存，请先推送商品库存(不校验批号)。");
                                        }
                                    }
                                    cmd.Parameters.Clear();

                                    cmd.CommandText = "INSERT INTO neus_jiangsu_orderdetail  VALUES (@orderno, @orderdetailno, @oldproductno,@tax, @productqty, @realprice, @realmoney, @lotno, @feetype, @create_time, @update_time)";
                                    cmd.Parameters.AddWithValue("@orderno", order.orderno);
                                    cmd.Parameters.AddWithValue("@orderdetailno", item.orderdetailno);
                                    cmd.Parameters.AddWithValue("@oldproductno", item.oldproductno);
                                    cmd.Parameters.AddWithValue("@tax", item.tax);
                                    cmd.Parameters.AddWithValue("@productqty", item.productqty);
                                    cmd.Parameters.AddWithValue("@realprice", item.realprice);
                                    cmd.Parameters.AddWithValue("@realmoney", item.realmoney);
                                    cmd.Parameters.AddWithValue("@lotno", item.lotno);
                                    cmd.Parameters.AddWithValue("@feetype", item.feetype);
                                    cmd.Parameters.AddWithValue("@create_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                    cmd.Parameters.AddWithValue("@update_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                    await cmd.ExecuteNonQueryAsync();
                                    cmd.Parameters.Clear();
                                }
                                catch (Exception ex)
                                {
                                    error = error + $" 【{item.oldproductno}:{item.oldproductname}】订单商品处理失败：" + ex.Message + "|";
                                }
                                cmd.Parameters.Clear();
                            }
                            if (error != "")
                            {
                                transaction.Rollback();
                                throw new Exception(error);

                            }
                            else
                            {
                                transaction.Commit();
                                return Json(new { code = 200, message = $"处理成功", data = "" });
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    string Errnum = LogHelper.GetErrornumber();
                    Console.WriteLine(ex.Message);
                    return Json(new { code = codestate, message = $"[{Errnum}]调用东软三方自定义服务失败！请检查！错误内容：{ex.Message} " , data = "" });

                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        await conn.CloseAsync();
                    }
                }
            }
        }

        /// <summary>
        /// 东软商品订单交易退款
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [Route("returnFee")]
        [HttpPost]
        public async Task<IHttpActionResult> returnFee(HttpRequestMessage request)
        {
            string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                try
                {
                    if (FsiDatainfo.stateNeusJiangsuApi == false)
                    {
                        throw new Exception("该接口已被控制台停用！请联系管理员！");
                    }
                    var NeusJiangsuHeader = JsonConvert.DeserializeObject<NeusJiangsuHeader>(request.Content.ReadAsStringAsync().Result);
                    string appKey = "";
                    string data = Base64toString(NeusJiangsuHeader.data);
                    //string data = Base64toString(request.Content.ReadAsStringAsync().Result);
                    if (Request.Headers.TryGetValues("AppKey", out var appKeyValues))
                    {
                        appKey = appKeyValues.FirstOrDefault();
                    }
                    if (string.IsNullOrEmpty(appKey) || string.IsNullOrEmpty(data))
                    {
                        throw new Exception("参数校验失败：请求的appkey或data节点为空！");
                    }
                    var order = JsonConvert.DeserializeObject<NeusJiangsuOrderdetail>(data);
                    int orderflag = order.saleFlag;
                    if (orderflag != 1)
                    {
                        throw new Exception("参数校验失败：传入的订单类型标志为销售，请调用销售接口");
                    }

                    List<NeusJiangsuOrderdetailList> list = new List<NeusJiangsuOrderdetailList>();
                    list = order.orderdetailList;
                    if (list == null)
                    {
                        throw new Exception("参数校验失败：传入的订单商品列表为空");
                    }
                    DateTime dateTime = DateTime.Now;
                    await conn.OpenAsync();
                    string error = "";
                    using (MySqlTransaction transaction = conn.BeginTransaction())
                    {
                        using (MySqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = "select * from neus_jiangsu_order where orderno=@orderno and originalOrderno='' ";
                            cmd.Parameters.AddWithValue("@orderno", order.originalOrderno);
                            using (MySqlDataReader reader = (MySqlDataReader)await cmd.ExecuteReaderAsync())
                            {
                                DataTable dt = new DataTable();
                                dt.Load(reader);
                                if (dt.Rows.Count < 1)
                                {
                                    throw new Exception($"查询订单表数据失败：根据传入的原单据编号{order.originalOrderno}未找到正交易订单或已被退费。");
                                }
                            }
                            cmd.Parameters.Clear();
                            cmd.Transaction = transaction;
                            try
                            {
                                cmd.CommandText = "INSERT INTO neus_jiangsu_order  VALUES ( @orderno, @orgcode,@createdate,@realmoney,@memo,@dtllines, @saleFlag,@originalOrderno,@create_time,@update_time)";
                                cmd.Parameters.AddWithValue("@orderno", order.orderno);
                                cmd.Parameters.AddWithValue("@orgcode", order.orgcode);
                                cmd.Parameters.AddWithValue("@createdate", order.createdate);
                                cmd.Parameters.AddWithValue("@realmoney", order.realmoney);
                                cmd.Parameters.AddWithValue("@memo", order.memo);
                                cmd.Parameters.AddWithValue("@dtllines", order.dtllines);
                                cmd.Parameters.AddWithValue("@saleFlag", order.saleFlag);
                                cmd.Parameters.AddWithValue("@originalOrderno", order.originalOrderno);
                                cmd.Parameters.AddWithValue("@create_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                cmd.Parameters.AddWithValue("@update_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                await cmd.ExecuteNonQueryAsync();
                                cmd.Parameters.Clear();
                            }
                            catch (Exception ex)
                            {
                                throw new Exception($"订单汇总数据异常：{ex.Message}");
                            }

                            foreach (var item in list)
                            {
                                try
                                {
                                    cmd.CommandText = "INSERT INTO neus_jiangsu_orderdetail  VALUES (@orderno, @orderdetailno, @oldproductno,@tax, @productqty, @realprice, @realmoney, @lotno, @feetype, @create_time, @update_time)";
                                    cmd.Parameters.AddWithValue("@orderno", order.orderno);
                                    cmd.Parameters.AddWithValue("@orderdetailno", item.orderdetailno);
                                    cmd.Parameters.AddWithValue("@oldproductno", item.oldproductno);
                                    cmd.Parameters.AddWithValue("@tax", item.tax);
                                    cmd.Parameters.AddWithValue("@productqty", item.productqty);
                                    cmd.Parameters.AddWithValue("@realprice", item.realprice);
                                    cmd.Parameters.AddWithValue("@realmoney", item.realmoney);
                                    cmd.Parameters.AddWithValue("@lotno", item.lotno);
                                    cmd.Parameters.AddWithValue("@feetype", item.feetype);
                                    cmd.Parameters.AddWithValue("@create_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                    cmd.Parameters.AddWithValue("@update_time", dateTime.ToString("yyyy-MM-dd HH-mm-ss"));
                                    await cmd.ExecuteNonQueryAsync();
                                    cmd.Parameters.Clear();
                                }
                                catch (Exception ex)
                                {
                                    error = error + $"商品订单明细编号{item.orderdetailno}商品{item.oldproductname}订单推送出错：" + ex.Message + "|";
                                }
                                cmd.Parameters.Clear();
                            }
                            if (error != "")
                            {
                                transaction.Rollback();
                                throw new Exception(error);

                            }
                            else
                            {
                                transaction.Commit();
                                return Json(new { code = 200, message = $"处理成功", data = "" });
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    string Errnum = LogHelper.GetErrornumber();
                    return Json(new { code = -1, message = $"[{Errnum}]调用东软三方自定义服务失败！请检查！错误内容：{ex.Message} " , data = "" });

                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        await conn.CloseAsync();
                    }
                }
            }
        }
    }
}
